package server;

import gui.Search_GUI;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import javax.swing.ImageIcon;
import javax.swing.JOptionPane;

import ocsf.server.ConnectionToClient;

import client.Answers;
import client.Cart;
import client.CreditCard;
import client.Delivery;
import client.Item;
import client.LogIn;
import client.Main;
import client.MemberAccount;
import client.MemberApproval;
import client.Order;
import client.SearchDetails;
import client.Subscribe;
import client.Survey;
import client.User;

/**
 * 
 * Work with Database
 *
 */
public class DBcheck 
{
	public static Statement stmt;	
	
	/**
	 * Gets user details and log him in
	 * @param obj
	 * @param conn Connect Object
	 * @return User Object
	 */
	public Object authenticate(Object obj, Connection conn)
	{
		User usr = (User)obj;
		
		if(usr.logout == false)
		{	
			try 
			{ 
				stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM user_data WHERE username='"+usr.getUserName()+"'and password='"+usr.getPassword()+"'");			
				
				if(rs.next())
				{
					if(!rs.getBoolean(6))	//if user not logged in
				    {
						usr.setID(rs.getInt(1));
						usr.setName(rs.getString(2));
						usr.setPriv(rs.getInt(5));
						usr.setStatus("OK");		//user is logged in
					   
						ResultSet rss = stmt.executeQuery("SELECT m.frozen FROM member m WHERE (m.uid='"+usr.getID()+"')and(m.frozen='"+1+"')");
						if(rss.next())
						{
							if(rss.getInt(1) == 1)
								usr.setStatus("FROZEN");
						}
						else
						{
							stmt.executeUpdate("UPDATE user_data SET loggedin='"+1+"'WHERE username='"+usr.getUserName()+"'");
						}
				    }
					else usr.setStatus("IN");
				}
				else usr.setStatus("WRONG");
			}  
			catch (SQLException e) {e.printStackTrace();}
		return usr;
		}
		else
		{
			try 
			{
				stmt = conn.createStatement();
				stmt.executeUpdate("UPDATE user_data SET loggedin='"+0+"'WHERE username='"+usr.getUserName()+"'");
			} 
			catch (SQLException e1) {e1.printStackTrace();}
			return usr;
		}
	}

	public static Object[] getLogedInUsers(Connection conn)
	{
		Object[] result = null;
		
		try 
		{	
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT u.id FROM user_data u WHERE loggedin='"+1+"'");	
			rs.last();
			result = new Object[rs.getRow()];
			rs.beforeFirst();
			int i = 0;
			
			while(rs.next())
				result[i++] = rs.getInt(1);
				
			}catch (SQLException e){e.printStackTrace();}
		return result;
	}
	
	public Object getSearchResult(Object obj, Connection conn) 
	{
		SearchDetails sd = (SearchDetails)obj; 
		String[] colors = sd.getcolorsSelected();
		String colorsString;
		String[] types = sd.gettypesSelected();
		String typesString;
		float min = sd.getminPrice();
		float max = sd.getmaxPrice();
		int itemId = sd.getitemId();
		String Query = null;
		ArrayList<Item> list = new ArrayList<Item>();
		
		if (colors == null || colors.length == 0)
		{
			colorsString = "'%"+Search_GUI.colors[0]+"%'";
            for (int i = 1; i < Search_GUI.colors.length; i++)
            	colorsString += " or i.color like'%"+Search_GUI.colors[i]+"%'";
		}
		else 
		{
			colorsString = "'%"+colors[0]+"%'";
            for (int i = 1; i < colors.length; i++)
            	colorsString += " or i.color like'%"+colors[i]+"%'";
		}
		
		if (types == null || types.length == 0)
		{
			typesString = "'%"+Search_GUI.types[0]+"%'";
            for (int i = 1; i < Search_GUI.types.length; i++)
            	typesString += " or i.type like'%"+Search_GUI.types[i]+"%'";
		}
		else 
		{
			typesString = "'%"+types[0]+"%'";
            for (int i = 1; i < types.length; i++)
            	typesString += " or i.type like'%"+types[i]+"%'";
		}
		
		if(sd.getCallFrom().equals("search") || sd.getCallFrom().equals("allItems"))
		{
			if(itemId == -1)	
			{	
				if(min == -1 && max == -1)
					Query = "SELECT i.id, i.name, i.price, i.type, i.incatalog, i.discount FROM item i WHERE (i.color like "+colorsString+")and( i.type like "+typesString+")";
				else if(min == -1)
					Query = "SELECT i.id, i.name, i.price, i.type, i.incatalog, i.discount FROM item i WHERE (i.price  <= '"+max+"')and( i.color like "+colorsString+")and( i.type like "+typesString+")";
				else if(max == -1)
					Query = "SELECT i.id, i.name, i.price, i.type, i.incatalog, i.discount FROM item i WHERE (i.price >= '"+min+"')and( i.color like "+colorsString+")and (i.type like "+typesString+")";
				else
					Query = "SELECT i.id, i.name, i.price, i.type, i.incatalog, i.discount FROM item i WHERE (i.price >= '"+min+"')and (i.price  <= '"+max+"')and( i.color like "+colorsString+")and( i.type like "+typesString+")";		
			}
			else
				Query = "SELECT i.id, i.name, i.price, i.type, i.incatalog, i.discount FROM item i WHERE i.id = '"+itemId+"'";
		}
		else 
			if(sd.getCallFrom().equals("catalog") || sd.getCallFrom().equals("inCatalog"))
				Query = "SELECT i.id, i.name, i.price, i.type, i.incatalog, i.discount FROM item i WHERE i.incatalog = '"+1+"'";
		else 
			if(sd.getCallFrom().equals("notInCatalog"))
				Query = "SELECT i.id, i.name, i.price, i.type, i.incatalog, i.discount FROM item i WHERE i.incatalog = '"+0+"'";
		else 
			if(sd.getCallFrom().equals("onSale"))
				Query = "SELECT i.id, i.name, i.price, i.type, i.incatalog, i.discount FROM item i WHERE i.discount != '"+0+"'";
		else 
			if(sd.getCallFrom().equals("notOnSale"))
				Query = "SELECT i.id, i.name, i.price, i.type, i.incatalog, i.discount FROM item i WHERE i.discount = '"+0+"'";
		try 	
		{ 
			stmt = conn.createStatement();
			ResultSet rss = stmt.executeQuery(Query);
			
			FileInputStream fisico = null;
			BufferedInputStream bisico = null;
			File iconFile = null;
			
			FileInputStream fisimg = null;
			BufferedInputStream bisimg = null;
			File imageFile = null;
			
			while(rss.next())
			{
				Item item = new Item(rss.getInt(1));
				
				iconFile = new File (Main.sets[3]+"/icon"+rss.getInt(1)+".jpg");
				item.initmyIconArray((int)iconFile.length());
				
				imageFile = new File (Main.sets[3]+"/img"+rss.getInt(1)+".jpg");
				item.initmyImagArray((int)imageFile.length());
				
				try 
				{
					fisico = new FileInputStream(iconFile);
					fisimg = new FileInputStream(imageFile);
				} 
				catch (FileNotFoundException e) {e.printStackTrace();}
				
				bisico = new BufferedInputStream(fisico);	
				bisimg = new BufferedInputStream(fisimg);	
				
				try 
				{
					bisico.read(item.myIconArray, 0, item.myIconArray.length);
					bisimg.read(item.myImagArray, 0, item.myImagArray.length);
				}
				catch (IOException e){e.printStackTrace();}

				item.setName(rss.getString(2));
				item.setPrice(rss.getFloat(3));
				item.setItemTypNname(rss.getString(4));
				item.inCatalog = rss.getInt(5);
				item.discount = rss.getInt(6);
				list.add(item);
			}
			rss.close();
		}
		catch (SQLException e) {}	
	return list;
	}
	
	/**
	 * List user in members list
	 * @param obj
	 * @param conn
	 * @return 1 if Member listed in members list,else 0
	 */
	public int createMemberAccount(Object obj, Connection conn)
	{
		MemberAccount ma=(MemberAccount)obj;
		try 
		{
			stmt = conn.createStatement();
			if(ma.cc!=null)
				stmt.executeUpdate("INSERT INTO creditcard VALUES(" + ma.getUserID() + ",'" + ma.getCreditCard().getcardNumber() + "','" + ma.getCreditCard().getexpirationDate() + "')");
			stmt.executeUpdate("INSERT INTO member VALUES(" + ma.getUserID() + ",0,0,0,0,0,'"+ma.address+"','"+ma.phone+"','"+ma.idnumber+"')");
			
			return 1;
		}
		catch (SQLException e) 
		{
			//e.printStackTrace();
			return 0;
		}
	}
	
	public int createNewCreditCard(Object obj, Connection conn)//returns 1 if new credit card saved
	{
		CreditCard cc=(CreditCard)obj;
		try 
		{
			stmt = conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT COUNT(c.uid) FROM creditcard c WHERE c.uid='"+(Integer)cc.uid+"'AND cardnumber='"+cc.getcardNumber()+"'");
			rs.next();
			if(rs.getInt(1)==0)
			{
				stmt.executeUpdate("INSERT INTO creditcard VALUES(" + (Integer)cc.uid + ",'" + cc.getcardNumber() + "','" + cc.getexpirationDate() + "')");
				return 1;
			}
			else return -1;
		}
		catch (SQLException e) 
		{
			e.printStackTrace();
			return 0;
		}
	}
	
	/**
	 * works as thread and checks for new complaint in DB
	 * @param conn
	 * @return number of new complaints in DB
	 */
	public int compThread(Connection conn)
	{
		try {
			stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT CID FROM complaint WHERE r=0");
			rs.last();
			int rows=rs.getRow();
			rs.beforeFirst();
			//while(rs.next())
			stmt.executeUpdate("UPDATE complaint SET r=1");
			return rows;
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}
	}
	
	public Integer[] getSurveySid( Connection conn1)
	{
	
		Integer[] survey = new Integer[10];
		int i=0;
		try 
		{ 
			stmt = conn1.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT s.Sid FROM survey s ");
			while(rs.next())
			{			
				survey[i++]=rs.getInt(1);
			}
			rs.close();
		}
		catch (SQLException e) {System.out.println("getUnAnalizedSid: SQL ERROR! ");}
		return survey;
	}

	public Object[][] getSurveyAnswers( int sn,Connection conn1)
	{
		Object[][]answers= null;
		try 
		{ 
			stmt = conn1.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT a.Aid,a.Q1,a.Q2,a.Q3,a.Q4,a.Q5,a.Q6 FROM answer a,survey s WHERE (s.Sid=a.Sid) AND (a.Sid='"+sn+"')");//(s.Sid=a.Sid) AND (s.Sname=survey1)");
			rs.last();						
			answers=new Object[rs.getRow()][7];
			rs.beforeFirst();
			int i=0;
			System.out.println("Answers: before for ");
			while (rs.next())
			{
				for(int j=1;j<8;j++)
				{			
					answers[i][j-1]=rs.getInt(j);
				}
				i++;
			}
			rs.close();
		}
		catch (SQLException e) {System.out.println("getSurveyAnswers: SQL ERROR! ");}
		System.out.println("answers end DB:  " +answers );
	return answers;	
	}
	/**
	 * 
	 * @param sn type of complaint
	 * @param conn1
	 * @return all complaints of the desirable type
	 */
	public Object[][] getComplaintDetails(String sn,Connection conn1)
	{
		Object[][] details = null;
		try
		{
			ResultSet rs;
			stmt = conn1.createStatement();
			if (sn.equals("handled"))
				 rs=stmt.executeQuery("SELECT c.CID,c.UID,c.description,c.response,c.compensation FROM complaint c WHERE c.status=0");
			else
				 rs=stmt.executeQuery("SELECT c.CID,c.UID,c.description,c.response,c.compensation FROM complaint c WHERE c.status=1");
			rs.last();
			details = new Object[rs.getRow()][5];
			rs.beforeFirst();
			for(int i=0;rs.next();i++)
			{
				for(int j=1;j<6;j++)
				{
					details[i][j-1] = rs.getString(j);
				}
			}
			rs.close();
		}
		catch (SQLException e) {System.out.println("getComplaintDetails: SQL ERROR! ");}
		return details;
	}
	/**
	 * user gets all of his complaints
	 * @param sn type of complaint
	 * @param id user id
	 * @param conn1
	 * @return all of the complaints of chosen type of the desirable user id
	 */
	public Object[][] getMyComplaints(String sn, String id, Connection conn1)
	{
		Object[][] details = null;
		int uid = Integer.parseInt(id);
		try
		{
			ResultSet rs;
			stmt = conn1.createStatement();
			if (sn.equals("handled"))
				 rs=stmt.executeQuery("SELECT c.CID,c.description,c.response FROM complaint c WHERE (c.status=0) AND (c.UID='" +uid+"')");
			else
				 rs=stmt.executeQuery("SELECT c.CID,c.description,c.response FROM complaint c WHERE (c.status=1) AND (c.UID='" +uid+"')");
			rs.last();
			details = new Object[rs.getRow()][3];
			rs.beforeFirst();
			for(int i=0;rs.next();i++)
			{
				for(int j=1;j<4;j++)
				{
					details[i][j-1] = rs.getString(j);
				}
			}
			rs.close();
		}
		catch (SQLException e) {System.out.println("getComplaintDetails: SQL ERROR! ");}
		return details;
	}
	
	/**
	 * Subscribes members
	 * @param obj
	 * @param conn
	 * @return 1 if user subscribed,else 0
	 */
	public int setSubscribe(Object obj,Connection conn)
	{
		Subscribe s=(Subscribe)obj;
		try {
			stmt=conn.createStatement();
			SimpleDateFormat d=new SimpleDateFormat("dd/MM/yy");
			Calendar c=Calendar.getInstance();
			String start=d.format(c.getTime());
			
			stmt.executeUpdate("UPDATE member SET subscribetype="+ s.getType() +",startDate='"+start+"' WHERE uid=" + s.getUserID());
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		
	}

	/**
	 * Gets members data
	 * @param conn
	 * @return data of members
	 */
	public Object[][] getMembersList(Connection conn)
	{
		Object[][] tbl=null;
		try {
			stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT m.uid,u.name,m.status FROM member m,user_data u WHERE m.uid=u.id ORDER BY status");
			
			rs.last();
			int rows=rs.getRow();
			rs.beforeFirst();
			tbl=new Object[rows][3];
			for(int i=0;rs.next();i++)
				for(int j=1;j<4;j++)
				{
					if(j!=3)
						tbl[i][j-1]=rs.getObject(j);
					else
						tbl[i][j-1]=rs.getBoolean(j);
				}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		return tbl;
	}
	
	/**
	 * Sets member status in the system (approved/disapproved)
	 * @param msg
	 * @param conn
	 * @return 1 if members approval/disapproval succeeded
	 */
	public int setMember(Object msg,Connection conn)
	{
		MemberApproval m=(MemberApproval)msg;
		try {
			int priv;
			if(m.getApprove()==1)
				priv=5;
			else
				priv=6;
			stmt=conn.createStatement();
			stmt.executeUpdate("UPDATE member SET status="+m.getApprove()+" WHERE uid="+m.getUID());
			stmt.executeUpdate("UPDATE user_data SET priv="+priv+" WHERE id="+m.getUID());
			return 1;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
	}
	
	/**
	 * Sets the member details in the delivery details
	 * @param msg
	 * @param conn
	 * @return member delivery details 
	 */
	public Delivery getMember(Object msg,Connection conn)
	{
		Delivery d=(Delivery)msg;
		try 
		{
			stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT m.address,m.phonenumber FROM member m WHERE m.uid="+d.getUID());
			rs.next();
			d.setRecAddress(rs.getString(1));
			d.setRecPhone(rs.getString(2));
			return d;
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
			return null;
		}
	}
	
	public Integer createAnswers(Object obj, Connection conn1)
	{
		Answers ans=(Answers)obj;
		try 
		{
			stmt = conn1.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT Aid FROM answer");
			rs.last();
			int index = rs.getRow();
			Integer q[]=ans.getQ();
			index++;
			
			String s = "INSERT INTO answer VALUES(" +index+ ",'" + ans.getSid() + "','" +q[0]+ "','" +q[1]+ "','" +q[2]+ "','" +q[3]+ "','" +q[4]+ "','" +q[5]+ "')";
			stmt.executeUpdate(s);
			return 1;	
		}
		catch (SQLException e)  
		{
			System.out.println("createAnswers: SQL ERROR! ");
			return 0;
		}
		
	}

	public int saveAnalyze(Object obj, Connection conn1) 
	{
		Survey s=(Survey)obj;
		try 
		{
			stmt = conn1.createStatement();
			stmt.executeUpdate("UPDATE survey  srv SET srv.analyze='"+s.getAnalyze()+"' WHERE srv.Sid='"+s.getSid()+"'");
			return 1;	
		}
		catch (SQLException e)  
		{
			System.out.println("save analyze: SQL ERROR! ");
			return 0;
		}
	}

	public Object getSurvey(String strSid,Connection conn1) 
	{
	  int sid=(Integer)Integer.parseInt(strSid);
		Survey s=new Survey(sid);
		try 
		{ 
			stmt = conn1.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT * FROM survey s WHERE s.Sid='"+sid+"'");
			rs.next();
			s.setAnalyse(rs.getString(3));
			String str[]=new String[6];
			for(int i=0;i<6;i++)	 str[i]=rs.getString(i+4);
			s.setQi(str);
			s.setDistribute(rs.getInt(10));
			rs.close();
		}
		catch (SQLException e) {System.out.println("display survey: SQL ERROR! ");}	
		return s;
	}
	
	public Object setOrderData(Object msg, Connection conn) 
	{
		Order or = (Order)msg;
		
		try 
		{ 
			String card = "card";
			int i = -1;
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT MAX(oid) FROM test.order");			
			if(rs.next())				
				i = rs.getInt(1);
			i++;
			stmt.executeUpdate("INSERT INTO test.order VALUES("+(Integer)or.usrId+",'" +i+"','" +(Integer)or.DeliveryGreetingData[0]+"','" +(Integer)or.DeliveryGreetingData[1]+"','" +(Float)or.total+"','" +card+"','" +0+"','" +(String)or.DeliveryGreetingData[7]+"','" +(String)or.DeliveryGreetingData[8]+"','" +0+"','" +(String)or.DeliveryGreetingData[9]+"','" +(String)or.DeliveryGreetingData[10]+"')");
		
			for(int j = 0 ; j < or.orderItems.length ; j++)
				stmt.executeUpdate("UPDATE usercart SET paid='"+1+"', oid='"+i+"' WHERE (uid='"+(Integer)or.usrId+"')and('"+(Integer)or.orderItems[j][0]+"'=itemid)");
			
			for(int j = 0 ; j < or.orderCustomItems.length ; j++)
				stmt.executeUpdate("UPDATE itemforcustomitem SET paid='"+1+"', oid='"+i+"' WHERE (uid='"+(Integer)or.usrId+"')and('"+(Integer)or.orderCustomItems[j][0]+"'=customitemid)");
		}
		catch (SQLException e) {}
		return msg;
	}
	
	public Object getOrderData(Object msg, Connection conn) 
	{
		Order or = (Order)msg;
		try 
		{
			int i = 0;
			stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT oid, createddate, readydate, totalprice FROM test.order WHERE (uid='"+(Integer)or.usrId+"')");
			rs.last();
			or.data = new Object[rs.getRow()][6];
			rs.beforeFirst();
			while(rs.next())
			{	
				or.data[i][0]=rs.getInt(1);
				or.data[i][1]=rs.getString(2);
				or.data[i][2]=rs.getString(3);
				or.data[i++][3]=rs.getFloat(4);
			}
			return or;
		} 
		catch (SQLException e) {e.printStackTrace();return null;}
	}
	
	public Object getUserCart(Object msg, Connection conn) 
	{
		Cart userCart = (Cart)msg;
		try 
		{
			int i = 0;
			FileInputStream fisico = null;
			BufferedInputStream bisico = null;
			File iconFile = null;
			
			stmt=conn.createStatement();													
			
			ResultSet rs = stmt.executeQuery("SELECT it.id, it.name, it.price, it.type, uc.quantity FROM item it, usercart uc WHERE (uc.uid='"+userCart.usrId+"')and(it.id=uc.itemid)and(uc.paid='"+0+"')");			
			rs.last();
			userCart.dataFromDB = new Object[rs.getRow()][8];
			rs.beforeFirst();
			
			while(rs.next())
			{
				userCart.dataFromDB[i][0] = rs.getInt(1);
				userCart.dataFromDB[i][1] = rs.getString(2);
				userCart.dataFromDB[i][4] = rs.getInt(5);
				userCart.dataFromDB[i][2] = rs.getFloat(3) * (float)rs.getInt(5);
				userCart.dataFromDB[i][3] = rs.getString(4);
				userCart.dataFromDB[i][5] = new Boolean(true);
				
				iconFile = new File (Main.sets[3]+"/icon"+rs.getInt(1)+".jpg");
				fisico = new FileInputStream(iconFile);
				bisico = new BufferedInputStream(fisico);
				userCart.myIconArray = new byte[(int)iconFile.length()];
				bisico.read(userCart.myIconArray, 0, (int)iconFile.length());
				
				userCart.dataFromDB[i][6] = new ImageIcon(userCart.myIconArray);
				userCart.dataFromDB[i++][7] = "";
			}
			rs.close();
			
			rs = stmt.executeQuery("SELECT ifci.customitemid, ifci.name, ifci.price, ifci.type, ifci.itemquantity, ifci.itemid FROM itemforcustomitem ifci WHERE (ifci.uid='"+userCart.usrId+"')and(ifci.paid='"+0+"')");		
			rs.last();
			userCart.customDataFromDB = new Object[rs.getRow()][5];
			userCart.QuantityCustomDataFromDB = new Object[rs.getRow()];
			userCart.customSingelItemIdDataFromDB = new Object[rs.getRow()];
			
			rs.beforeFirst();
			i = 0;
			while(rs.next())
			{
				userCart.customDataFromDB[i][0] = rs.getInt(1);
				userCart.customDataFromDB[i][1] = rs.getString(2);
				userCart.customDataFromDB[i][2] = rs.getFloat(3);
				userCart.customDataFromDB[i][3] = rs.getString(4);
				userCart.customDataFromDB[i][4] = "";
				userCart.QuantityCustomDataFromDB[i] = rs.getInt(5);
				userCart.customSingelItemIdDataFromDB[i++] = rs.getInt(6);
				userCart.cuonter++;
			}
			rs.close();
			
			rs = stmt.executeQuery("SELECT DISTINCT ifci.customitemid FROM itemforcustomitem ifci WHERE (ifci.uid='"+userCart.usrId+"')and(ifci.paid='"+0+"')");		
			rs.last();
			userCart.DISTINCTcustomDataFromDB = new Integer[rs.getRow()];
			rs.beforeFirst();
			i = 0;		
			while(rs.next())
				userCart.DISTINCTcustomDataFromDB[i++] = rs.getInt(1);
			rs.close();
			
			rs = stmt.executeQuery("SELECT DISTINCT ifci.name FROM itemforcustomitem ifci WHERE (ifci.uid='"+userCart.usrId+"')and(ifci.paid='"+0+"')");		
			rs.last();
			userCart.DISTINCTcustomDataNameFromDB = new Object[rs.getRow()];
			rs.beforeFirst();
			i = 0;
			
			while(rs.next())
				userCart.DISTINCTcustomDataNameFromDB[i++] = rs.getString(1);
			rs.close();
		} 
		catch (SQLException e) {e.printStackTrace();} 
		catch (FileNotFoundException e) {e.printStackTrace();} 
		catch (IOException e) {e.printStackTrace();}
		
		return userCart;
	}
	
	public Object getItemsInCustomItem(Object msg, Connection conn) 
	{
		Cart userCart = (Cart)msg;
		
		int i = 0;
		FileInputStream fisico = null;
		BufferedInputStream bisico = null;
		File iconFile = null;
		
		try 
		{
			stmt=conn.createStatement();													
			
			ResultSet rs = stmt.executeQuery("SELECT it.id, it.name, ifci.price, it.type, ifci.itemquantity FROM item it, itemforcustomitem ifci WHERE (ifci.uid='"+userCart.usrId+"')and(ifci.customitemid='"+userCart.customItemId+"')and(it.id=ifci.itemid)");			
			rs.last();
			userCart.itemsInCustomItemFromDB = new Object[rs.getRow()][7];
			rs.beforeFirst();
			
			while(rs.next())
			{
				userCart.itemsInCustomItemFromDB[i][0] = rs.getInt(1);
				userCart.itemsInCustomItemFromDB[i][1] = rs.getString(2);
				userCart.itemsInCustomItemFromDB[i][2] = rs.getFloat(3);
				userCart.itemsInCustomItemFromDB[i][3] = rs.getString(4);
				userCart.itemsInCustomItemFromDB[i][4] = rs.getInt(5);
				userCart.itemsInCustomItemFromDB[i][6] = "Remove";
				
				iconFile = new File (Main.sets[3]+"/icon"+rs.getInt(1)+".jpg");
				fisico = new FileInputStream(iconFile);
				bisico = new BufferedInputStream(fisico);
				userCart.myIconArray = new byte[(int)iconFile.length()];
				bisico.read(userCart.myIconArray, 0, (int)iconFile.length());
				
				userCart.itemsInCustomItemFromDB[i++][5] = new ImageIcon(userCart.myIconArray);
			}
			rs.close();
		}
		catch (SQLException e) {e.printStackTrace();} 
		catch (FileNotFoundException e) {e.printStackTrace();} 
		catch (IOException e) {e.printStackTrace();}	
		return userCart;
	}
	
	public Integer setUserCart(Object msg, Connection conn)
	{
		Cart userCart = (Cart)msg;
		int result = 0;
		
		if((Integer)userCart.dataToDB[2] != 0)//quantity != 0
		{
			try 
			{ 
				stmt = conn.createStatement();
				stmt.executeUpdate("INSERT INTO usercart VALUES("+(Integer)userCart.dataToDB[0]+",'" +(Integer)userCart.dataToDB[1]+ "','" +(Integer)userCart.dataToDB[2]+"','" +(Float)userCart.dataToDB[3]+"',0,0)");
				result = 1;
			}
			catch (SQLException e) {result = 0;}
		}
		if(result == 0)
		{
			try 
			{
				if((Integer)userCart.dataToDB[2] != 0)//quantity != 0
				{	
					stmt.executeUpdate("UPDATE usercart SET quantity='"+((Integer)userCart.dataToDB[2]) +"', price='"+((Float)userCart.dataToDB[3]) +"' WHERE (uid='"+userCart.usrId+"')and('"+(Integer)userCart.dataToDB[1]+"'=itemid)");
					result = 2;											
				}	
				else
				{	
					stmt.executeUpdate("DELETE FROM usercart WHERE (uid='"+userCart.usrId+"')and('"+(Integer)userCart.dataToDB[1]+"'=itemid)");
					result = 3;				
				}	
			}catch (SQLException e) {e.printStackTrace();}
		}
		return result;
	}
	
	public Object setCustomUserCart(Object msg, Connection conn) 
	{
		Cart userCart = (Cart)msg;
		int result = 0;
		
		if((Integer)userCart.customDataToDB[6] != 0)//quantity != 0
		{
			try 
			{ 
				stmt = conn.createStatement();
				stmt.executeUpdate("INSERT INTO itemforcustomitem VALUES("+(Integer)userCart.customDataToDB[0]+",'" 
																		  +(Integer)userCart.customDataToDB[1]+ "','" 
																		  +(Integer)userCart.customDataToDB[2]+"','" 
																		  +(Float)userCart.customDataToDB[3]+"','" 
																		  +(String)userCart.customDataToDB[4]+"','" 
																		  +(String)userCart.customDataToDB[5]+"','" 
																		  +(Integer)userCart.customDataToDB[6]+"',0,0)");
				result = 1;
			}
			catch (SQLException e) {result = 0;}
		}
		if(result == 0)
		{
			try 
			{
				if((Integer)userCart.customDataToDB[6] != 0)//quantity != 0
				{	
					stmt.executeUpdate("UPDATE itemforcustomitem SET itemquantity='"+((Integer)userCart.customDataToDB[6]) +"' WHERE (uid='"+userCart.usrId+"')and('"+(Integer)userCart.customDataToDB[1]+"'=customitemid)and('"+(Integer)userCart.customDataToDB[2]+"'=itemid)");
					result = 2;											
				}	
				else
				{	
					stmt.executeUpdate("DELETE FROM itemforcustomitem WHERE (uid='"+userCart.usrId+"')and('"+(Integer)userCart.customDataToDB[1]+"'=customitemid)and('"+(Integer)userCart.customDataToDB[2]+"'=itemid)");
					result = 3;				
				}	
			}catch (SQLException e) {e.printStackTrace();}
		}
		return result;
	}	
	
	public Integer updateCatalog(Object msg, Connection conn) 
	{
		Item item = (Item)msg;
		int result = 0;
		
		try 
		{
			stmt = conn.createStatement();
			stmt.executeUpdate("UPDATE item i SET i.price='"+((Float)item.newPrice) +"', i.incatalog='"+((Integer)item.inCatalog) +"', i.discount='"+((Integer)item.discount) +"'  WHERE (i.id='"+(Integer)item.getId()+"')");
			result = 1;
		} 
		catch (SQLException e) {e.printStackTrace();}
		return result;
	}
	
	public Object removeCustomItemFromCart(Object msg, Connection conn) 
	{
		Cart userCart = (Cart)msg;
		int result = 1;
		
		try 
		{
			stmt = conn.createStatement();
			stmt.executeUpdate("DELETE FROM itemforcustomitem WHERE (uid='"+userCart.usrId+"')and('"+(Integer)userCart.customItemIdToRemove+"'=customitemid)");
		
		}catch (SQLException e) {e.printStackTrace(); return result = 0;}
		
		return result;
	}
	
	public Object removeItemFromCustom(Object msg, Connection conn) 
	{
		Cart userCart = (Cart)msg;
		int result = 1;
		
		try 
		{
			stmt = conn.createStatement();
			if(userCart.itemsInCustomItemToRemove[2] == userCart.itemsInCustomItemToRemove[3])
				stmt.executeUpdate("DELETE FROM itemforcustomitem WHERE (uid='"+userCart.usrId+"')and('"+(Integer)userCart.itemsInCustomItemToRemove[1]+"'=customitemid)and('"+(Integer)userCart.itemsInCustomItemToRemove[0]+"'=itemid)");
			else
			{
				stmt.executeUpdate("UPDATE itemforcustomitem SET itemquantity='"+((Integer)userCart.itemsInCustomItemToRemove[3] - (Integer)userCart.itemsInCustomItemToRemove[2]) +"' WHERE (uid='"+userCart.usrId+"')and('"+(Integer)userCart.itemsInCustomItemToRemove[1]+"'=customitemid)and('"+(Integer)userCart.itemsInCustomItemToRemove[0]+"'=itemid)");
				result = 2;	
			}
		}catch (SQLException e) {e.printStackTrace(); return result = 0;}
		
		return result;
	}
	
	public Object removeItemFromCart(Object msg, Connection conn)
	{
		Cart userCart = (Cart)msg;
		int result = 1;
		
		try 
		{
			stmt = conn.createStatement();
			if(userCart.itemsInCartToRemove[1] == userCart.itemsInCartToRemove[2])
				stmt.executeUpdate("DELETE FROM usercart WHERE (uid='"+userCart.usrId+"')and('"+(Integer)userCart.itemsInCartToRemove[0]+"'=itemid)");
			else
			{
				stmt.executeUpdate("UPDATE usercart SET quantity='"+((Integer)userCart.itemsInCartToRemove[2] - (Integer)userCart.itemsInCartToRemove[1]) +"' WHERE (uid='"+userCart.usrId+"')and('"+(Integer)userCart.itemsInCartToRemove[0]+"'=itemid)");
				result = 2;	
			}
		}catch (SQLException e) {e.printStackTrace(); return result = 0;}
		
		return result;
	}
	/**
	 * sets or deletes a respond for a complaint
	 * @param msg complaint id
	 * @param msg1 response description
	 * @param conn 
	 * @return 0 if response added , 1 if deleted
	 */
	public int setResponse(String msg, String msg1, Connection conn)
	{
		java.sql.Date sqlDate = new java.sql.Date(new java.util.Date().getTime());
		java.sql.Time sqlTime = new java.sql.Time(new java.util.Date().getTime());
		
		int a=0;
		try {
			int cid = Integer.parseInt(msg.toString());
			String response = msg1.toString();
			stmt=conn.createStatement();
			PreparedStatement ps = conn.prepareStatement("UPDATE complaint SET response = ? , status = ? , responsedate = ? , respondtime = ?  WHERE CID="+cid);
			if(!response.isEmpty())
			{
				ps.setInt(2,0);
				a=0;
			}
			else
			{	
				ps.setInt(2,1);
				a=1;
			}
			ps.setString(1,response);
			ps.setDate(3, sqlDate);
			ps.setTime(4, sqlTime);
			ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {e.printStackTrace();}
		
		return a;
	}
	/**
	 * sets a new complaint
	 * @param msg description of complaint
	 * @param msg1 user id
	 * @param conn
	 * @return 1 if user exists, 0 if not
	 */
	public int setComplaint(String msg, String msg1, Connection conn)
	{
	
		java.sql.Date sqlDate = new java.sql.Date(new java.util.Date().getTime());
		java.sql.Time sqlTime = new java.sql.Time(new java.util.Date().getTime());
		
		
		try {
		stmt=conn.createStatement();
		ResultSet rs=stmt.executeQuery("SELECT name FROM user_data  WHERE id='"+Integer.parseInt(msg1.toString())+"'");
		rs.last();
		if(rs.getRow()==0)
			return 0;
		} 
		catch (SQLException e) {e.printStackTrace();}
		
		String query = "INSERT INTO complaint ("
		    + " CID,"
		    + " UID,"
		    + " description,"
		    + " status,"
		    + " response,"
		    + " daterecieve,"
		    + " timerecieve) VALUES ("
		    + "null, ?, ?, ?, ?, ?, ?)";
		
		try {
			
			 PreparedStatement st = conn.prepareStatement(query);
			 st.setInt(1,Integer.parseInt(msg1.toString()));
			 st.setString(2, msg.toString());
			 st.setInt(3, 1);
			 st.setString(4, " ");
			 st.setDate(5, sqlDate);
			 st.setTime(6, sqlTime);
			 st.executeUpdate();
			 st.close();
			 return 1;
			
		} catch (SQLException e) {e.printStackTrace(); return 0;}
		
	}
	/**
	 * gets frozen accounts details
	 * @param id user id or string
	 * @param conn
	 * @return if string all frozen accounts if user id the specific details of user
	 */

	public static Object[][] getUserDetails(String id,Connection conn)
	{
		Object[][] tbl=null;
		if(id.equals("get frozen accounts"))
		{
			try {
				stmt=conn.createStatement();
				ResultSet rs=stmt.executeQuery("SELECT m.uid,u.name,m.frozen FROM member m,user_data u WHERE (m.uid=u.id) AND (m.frozen=1)");
				rs.last();
				int rows=rs.getRow();
				rs.beforeFirst();
				tbl=new Object[rows][3];
				for(int i=0;rs.next();i++)
					for(int j=1;j<4;j++)
					{
						if(j!=3)
							tbl[i][j-1]=rs.getObject(j);
						else
							tbl[i][j-1]=rs.getBoolean(j);
					}
				
			} catch (SQLException e) {e.printStackTrace();}	
		}
		
		else
		{
			int uid = Integer.parseInt(id);
			try {
				stmt=conn.createStatement();
				ResultSet rs=stmt.executeQuery("SELECT m.uid,u.name,m.frozen FROM member m,user_data u WHERE (m.uid='" +uid+ "') AND  (m.uid=u.id) AND (m.status=1) ORDER BY status");
				rs.last();
				int rows=rs.getRow();
				rs.beforeFirst();
				tbl=new Object[rows][3];
				for(int i=0;rs.next();i++)
					for(int j=1;j<4;j++)
					{
						if(j!=5)
							tbl[i][j-1]=rs.getObject(j);
						else
							tbl[i][j-1]=rs.getBoolean(j);
					}
				
			} catch (SQLException e) {e.printStackTrace();}
		}
		return tbl;
	}
	
	public Integer DistributeSurvey(int sInt, Connection conn1) 
	{
		
		try 
		{
			stmt = conn1.createStatement();
			stmt.executeUpdate("UPDATE survey  srv SET srv.distribute='"+1+"' WHERE srv.Sid='"+sInt+"'");
			return 1;	
		}
		catch (SQLException e)  
		{
			System.out.println("Distribute Survey: SQL ERROR! ");
			return 0;
		}
	}

	public Integer getDistribute(int sInt, Connection conn1) {
		try 
		{
			Integer d;
			stmt = conn1.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT srv.distribute FROM survey  srv  WHERE srv.Sid='"+sInt+"'");
			rs.first();
			d=rs.getInt(1);
			return d;	
		}
		catch (SQLException e)  
		{
			System.out.println("get Distribute Survey: SQL ERROR! ");
			return null;
		}
	}
	/**
	 * freeze or recover a user account
	 * @param id user id
	 * @param fon 1 if to freeze, 0 if to recover
	 * @param conn
	 */
	public void setUserFrozen(String id,String fon,Connection conn)	//fon = frozen or not
	{
		int uid = new Integer(id.toString());
		try {
		stmt=conn.createStatement();
		PreparedStatement ps = conn.prepareStatement("UPDATE member SET frozen = ? WHERE uid="+uid);
		ps.setBoolean(1, Boolean.parseBoolean(fon));
		ps.executeUpdate();
		ps.close();			
		} catch (SQLException e) {e.printStackTrace();}
	}

	public Object getSurveyDistributedSid(Connection conn1) {

		Integer[] surveySid = new Integer[10];
		int i=0;
		try 
		{ 
			stmt = conn1.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT s.Sid FROM survey s WHERE s.distribute=1");
			while(rs.next())
			{			
				surveySid[i++]=rs.getInt(1);
			}
			rs.close();
		}
		catch (SQLException e) {System.out.println("getUnAnalizedSid: SQL ERROR! ");}
		return surveySid;
	}

	public Integer checkRead(Connection conn1) 
	{
		Integer readflag=0;
		try 
		{ 
			stmt = conn1.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT s.Sid FROM survey s WHERE s.distribute=1 AND s.readsurvey=0");
			if(rs.equals(null))	return readflag;
			while(rs.next())
			{			
				readflag++;
			}
			rs.close();
		}
		catch (SQLException e) {System.out.println("check if survey read : SQL ERROR! ");}
		return readflag;

	}

	public Integer readSurvey(int int1, Connection conn1)
	{
		Integer b=0;
		try 
		{ 
			stmt = conn1.createStatement();
			stmt.executeUpdate("UPDATE survey  SET readsurvey='"+1+"' WHERE Sid='"+int1+"'");
		}
		catch (SQLException e) {System.out.println("read survey : SQL ERROR! ");}
		System.out.println("end db msg = read Survey");
		return b;
	}
	/**
	 * 
	 * @param id user id
	 * @param conn
	 * @return user id,user name and his privilege
	 */
	public Object[][] getUserPriv(String id,Connection conn)
	{
		int uid = Integer.parseInt(id);
		Object[][] tbl=null;
			try {
				stmt=conn.createStatement();
				ResultSet rs=stmt.executeQuery("SELECT u.id,u.name,u.priv FROM user_data u WHERE u.id='"+uid+"'");
				rs.last();
				int rows=rs.getRow();
				rs.beforeFirst();
				tbl=new Object[rows][3];
				for(int i=0;rs.next();i++)
					for(int j=1;j<4;j++)
					{
						tbl[i][j-1]=rs.getObject(j);
					}
				
			} catch (SQLException e) {e.printStackTrace();}	
			return tbl;
		}
	/**
	 * change user privilege
	 * @param id user id
	 * @param priv new privilege between 1 and 6 to set 
	 * @param conn 
	 */
	public void changeUserPriv(String id,String priv,Connection conn)
	{
		try {
			int uid = Integer.parseInt(id);
			int uPriv = Integer.parseInt(priv);
			stmt=conn.createStatement();
			stmt.executeUpdate("UPDATE user_data SET priv="+uPriv+" WHERE id="+uid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * compensate a costumer or delete a compensation
	 * @param id user id
	 * @param mon amount of money to add or to reduce
	 * @param c complaint id
	 * @param conn
	 */
	public void setCompensation(String id, String mon, String c, Connection conn)	
	{
		int uid = Integer.parseInt(id);
		double money = Double.parseDouble(mon);
		int cid = Integer.parseInt(c);
		try {
		stmt=conn.createStatement();
		PreparedStatement ps = conn.prepareStatement("UPDATE member SET debt = (debt + "+money+") WHERE uid="+uid);
		ps.executeUpdate();
		if(cid!=-1)
		{
			ps = conn.prepareStatement("UPDATE complaint SET compensation = "+money+" WHERE (UID = "+uid+") AND (CID = "+cid+")");
			ps.executeUpdate();
		}
		ps.close();			
		} catch (SQLException e) {e.printStackTrace();}
	}
	
	public Object[][] getCostumerComplaintReport(String Smonth, String Emonth, String type, Connection conn)
	{
		int stat = 0;
		ResultSet rs;
		int sm = Integer.parseInt(Smonth);
		int em = Integer.parseInt(Emonth);
		Object[][] tbl=null;
		if(type.equals("Handled"))
			stat = 0;
		else
			stat = 1;
			
		try {
			stmt=conn.createStatement();
			if(!Emonth.equals(" "))
				rs=stmt.executeQuery("SELECT c.CID,c.description,c.response,c.compensation FROM complaint c WHERE status='"+stat+"' AND saved = 0 AND (MONTH(daterecieve)>='"+sm+"') AND (MONTH(daterecieve)<='"+em+"') ORDER BY status");
			
			else
				rs=stmt.executeQuery("SELECT c.CID,c.description,c.response,c.compensation FROM complaint c WHERE status='"+stat+"' AND saved = 0 AND (MONTH(date)='"+sm+"') ORDER BY status ");
			rs.last();
			int rows=rs.getRow();
			rs.beforeFirst();
			tbl=new Object[rows][4];
			for(int i=0;rs.next();i++)
				for(int j=1;j<5;j++)
				{
					//if(j!=3)
						tbl[i][j-1]=rs.getObject(j);
				//	else
					//[i][j-1]=rs.getBoolean(j);
				}
			
		} catch (SQLException e) {e.printStackTrace();}	
		
		return tbl;
	}

	public Object[] checkSubscribe(int id, Connection conn)
	{
		Object[] tbl=new Object[9];
		try {
				stmt=conn.createStatement();
				ResultSet rs=stmt.executeQuery("SELECT * FROM member m WHERE m.uid='"+id+"'");
				tbl[0]=-1;
				while(rs.next())
				{
					for(int j=0;j<9;j++)
					{
						tbl[j]=rs.getObject(j+1);
					}
				}
			} catch (SQLException e) {System.out.println("check Subscribe : SQL ERROR! ");}	
		return tbl;
	}
	/**
	 * save the reports in DB
	 * @param sn - the report type (String)
	 * @param conn
	 */
	public void saveReport(String []sn, Connection conn)
	{
		java.sql.Date sqlDate = new java.sql.Date(new java.util.Date().getTime());
		PreparedStatement ps = null;
		ResultSet rs = null;
		Date date, rDate;
		Time time, rTime;
		int stat = 0;
		
		try {
			stmt=conn.createStatement();
			if(sn[0].equals("save costumer report"))
			{
				rs=stmt.executeQuery("SELECT daterecieve, timerecieve, responsedate, respondtime, status FROM complaint WHERE CID='"+Integer.parseInt(sn[2])+"'");
				rs.next();
				date = rs.getDate(1);
				time = rs.getTime(2);
				stat = rs.getInt(5);
				rDate = rs.getDate(3);
				rTime = rs.getTime(4);
				rs.close();

				stmt.executeUpdate("INSERT INTO complaintreport VALUES("+Integer.parseInt(sn[1])+",'" +Integer.parseInt(sn[2])+ "','" +date+"','" +time+"','"+Integer.parseInt(sn[3])+"','"+rDate+"','"+rTime+"','"+stat+"','"+sqlDate+"')");

				ps = conn.prepareStatement("UPDATE complaint SET saved = 1 WHERE (CID = "+Integer.parseInt(sn[2])+")");
				ps.executeUpdate();
				ps.close();			
			}
			else if(sn[0].equals("save satisfaction report"))
			{
				stmt.executeUpdate("INSERT INTO satisfactionreport VALUES("+Integer.parseInt(sn[8])+",'" +(sn[1])+ "','" +(sn[2])+ "','" +(sn[3])+ "','" +(sn[4])+"','" +(sn[5])+ "','" +(sn[6])+"',"+(sn[7])+",'"+sqlDate+"')");
				 
			    String sql = "DELETE FROM answer WHERE Sid=?";  
				PreparedStatement pstmt = conn.prepareStatement(sql);
			    pstmt.setInt(1, Integer.parseInt(sn[1]));
			    pstmt.executeUpdate();
			   
			}
			else if(sn[0].equals("save inventory report"))
			{
				stmt.executeUpdate("INSERT INTO inventoryreport VALUES("+Integer.parseInt(sn[1])+",'" +(sn[2])+ "','" +(sn[3])+ "','" +Integer.parseInt((sn[4]))+ "','" +sqlDate+"')");	 
			}
			else if(sn[0].equals("save income report"))
			{
				String query = "INSERT INTO quarterincomereport ("
				    + " Rid,"
				    + " name,"
				    + " color,"
				    + " quantity,"
				    + " income,"
				    + " date) VALUES ("
				    + "?, ?, ?, ?, ?, ?)";
				
				try {
					
					 PreparedStatement st = conn.prepareStatement(query);
					 st.setInt(1,Integer.parseInt(sn[1]));
					 st.setString(2, sn[2]);
					 st.setString(3, sn[3]);
					 st.setInt(4, Integer.parseInt(sn[4]));
					 st.setString(5, (sn[5]));
					 st.setDate (6, sqlDate);
					 st.executeUpdate();
					 st.close();
					 
				
				} catch (SQLException e) {e.printStackTrace();}

			}
			else if(sn[0].equals("save order report"))
			{
				String query = "INSERT INTO orderreport ("
				    + " Rid,"
				    + " oid,"
				    + " itemid, "
				    + " name,"
				    + " color,"
				    + " quantity, "
				    + " paid, "
				    + " date) VALUES ("
				    + "?, ?, ?, ?, ?, ?, ?, ?)";
				
				try {
					
					 PreparedStatement st = conn.prepareStatement(query);
					 st.setInt(1,Integer.parseInt(sn[1]));
					 st.setInt(2, Integer.parseInt(sn[2]));
					 st.setInt(3, Integer.parseInt(sn[3]));
					 st.setString(4, sn[4]);
					 st.setString(5, sn[5]);
					 st.setInt(6, Integer.parseInt(sn[6]));
					 st.setBoolean(7, Boolean.parseBoolean(sn[7]));
					 st.setDate (8, sqlDate);
					 st.executeUpdate();
					 st.close();
					 
				} catch (SQLException e) {e.printStackTrace();}
			}

			
		}   
		catch (SQLException e) {System.out.println("delivery remove : SQL ERROR! ");}
		
	}	
	/**
	 * gets the next free report id
	 * @param num index for report type 1-complaint,2-satisfaction,3-inventory,4-quarter income,5-order
	 * @param conn
	 * @return the id to use
	 */
	public int getReportID(String num, Connection conn)
	{
		int crID = 1;
		ResultSet rs = null;
		try {
			stmt=conn.createStatement();
			if(num.equals("1"))
				rs=stmt.executeQuery("SELECT crID FROM complaintreport GROUP BY crID");
			if(num.equals("2"))
				rs=stmt.executeQuery("SELECT rid FROM satisfactionreport GROUP BY rid");
			if(num.equals("3"))
				rs=stmt.executeQuery("SELECT Rid FROM inventoryreport GROUP BY Rid");
			if(num.equals("4"))
				rs=stmt.executeQuery("SELECT Rid FROM quarterincomereport GROUP BY Rid");
			if(num.equals("5"))
				rs=stmt.executeQuery("SELECT Rid FROM orderreport GROUP BY Rid");
			rs.last();
			if(rs.getRow()!=0)
			{
				crID = rs.getInt(1);
				crID++;
			}
			
		}   
		catch (SQLException e) {System.out.println("delivery remove : SQL ERROR! ");}
		return crID;
	}
	/**
	 * 
	 * @param conn
	 * @return all items with names,color and quantity
	 */
	public Object[][] getInventoryRep(Connection conn)
	{
		Object[][] tbl=null;
		try {
			stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT i.name,i.color,i.quantity FROM item i");
			rs.last();
			int rows=rs.getRow();
			rs.beforeFirst();
			tbl= new Object[rows][3];
			int i=0;
			while(rs.next())
			{
				tbl[i][0]=rs.getObject(1);
				tbl[i][1]=rs.getObject(2);
				tbl[i][2]=rs.getObject(3);
				i++;
			}
			return tbl;
		} catch (SQLException e) {
			e.printStackTrace();
			return tbl;
		}
	}
	/**
	 * for each survey gets the average result of each answer
	 * @param conn
	 * @return for each question the average answer result
	 */
	public Object [][] getSatisfactionRep(Connection conn)
	{
		Object[][] tbl=null;
		try {
		stmt=conn.createStatement();
		ResultSet rs=stmt.executeQuery("SELECT Sid, AVG(Q1),AVG(Q2),AVG(Q3),AVG(Q4),AVG(Q5),AVG(Q6) FROM answer GROUP BY Sid");
		rs.last();
		int rows=rs.getRow();
		rs.beforeFirst();
		tbl=new Object[rows][7];
		for(int i=0;rs.next();i++)
			for(int j=1;j<8;j++)
			{
				tbl[i][j-1]=rs.getObject(j);
			}
		}   
		catch (SQLException e) {System.out.println("delivery remove : SQL ERROR! ");}
		return tbl;
	}
	/**
	 * gets all items that were sold and income in the desirable quarter
	 * @param quarter quarter of a year
	 * @param conn
	 * @return item id,name,color,sum of sales,sum of price paid
	 */
	public Object [][] getQuarterIncomeReport(String quarter, Connection conn)
	{
		Object[][] tbl=null;
		int first=0;
		int second=0;
		if(quarter.equals("First"))
		{
			first=1;
			second=3;	
		}
		if(quarter.equals("Second"))
		{
			first=4;
			second=6;	
		}
		if(quarter.equals("Third"))
		{
			first=7;
			second=9;	
		}if(quarter.equals("Forth"))
		{
			first=10;
			second=12;	
		}
			
		try {
			stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery("select  id, name, color, sum(quantity), sum(price) from (SELECT i.id,i.name,i.color,SUM(u.quantity)as quantity,SUM(u.price) as price FROM item i,usercart u,`order` o  WHERE (i.id=u.itemid) AND (u.uid=o.uid) AND (u.oid=o.oid) AND (u.paid=1) AND (MONTH(o.readydate)>='"+first+"') AND (MONTH(o.readydate)<='"+second+"') GROUP BY i.id union SELECT i.id,i.name,i.color,SUM(ic.itemquantity)as quantity,SUM(ic.price) as price FROM item i,itemforcustomitem ic, `order` o WHERE (i.id=ic.itemid) AND (ic.uid=o.uid) AND (ic.oid=o.oid) AND (ic.paid=1) AND (MONTH(o.readydate)>='"+first+"') AND (MONTH(o.readydate)<='"+second+"') GROUP BY i.id) hhhh Group by id,name,color");
			rs.last();
			int rows=rs.getRow();
			rs.beforeFirst();
			tbl=new Object[rows][5];
			for(int i=0;rs.next();i++)
				for(int j=1;j<6;j++)
				{
					tbl[i][j-1]=rs.getObject(j);
				}
		}   
		catch (SQLException e) {System.out.println("delivery remove : SQL ERROR! ");}
		return tbl;
	}
	/**
	 * gets satisfaction report that was created in a specific month
	 * @param conn
	 * @param num month
	 * @return report id,survey id,average results of answers and created date
	 */
	public Object[][] mngSatRep(Connection conn,String num)
	{
		Object[][] tbl=null;
		try {
		stmt=conn.createStatement();
		ResultSet rs=stmt.executeQuery("SELECT * FROM satisfactionreport WHERE MONTH(date)="+Integer.parseInt(num));
		rs.last();
		int rows=rs.getRow();
		rs.beforeFirst();
		tbl=new Object[rows][9];
		for(int i=0;rs.next();i++)
			for(int j=1;j<10;j++)
			{
				tbl[i][j-1]=rs.getObject(j);
			}
		}   
		catch (SQLException e) {e.printStackTrace();}
		return tbl;
	}
	/**
	 * gets inventory report of desirable month
	 * @param month month the report was made
	 * @param conn
	 * @return report id, item name,color,quantity,date created
	 */
	public Object[][] mngInvRep(String month,Connection conn)
	{
		Object[][] tbl=null;
		try {
		stmt=conn.createStatement();
		ResultSet rs=stmt.executeQuery("SELECT * FROM inventoryreport WHERE MONTH(date)="+Integer.parseInt(month));
		rs.last();
		int rows=rs.getRow();
		rs.beforeFirst();
		tbl=new Object[rows][5];
		for(int i=0;rs.next();i++)
			for(int j=1;j<6;j++)
			{
				tbl[i][j-1]=rs.getObject(j);
			}
		}   
		catch (SQLException e) {e.printStackTrace();}
		return tbl;
	}
	/**
	 * get order report from desirable month
	 * @param month month the report was created
	 * @param conn
	 * @return report id,order id,item id,name,color,quantity,if paid and date created
	 */
	public Object[][] mngOrdRep(String month, Connection conn)
	{
		Object[][] tbl=null;
		try {
		stmt=conn.createStatement();
		ResultSet rs=stmt.executeQuery("SELECT DISTINCT * FROM orderreport WHERE MONTH(date)="+Integer.parseInt(month));
		rs.last();
		int rows=rs.getRow();
		rs.beforeFirst();
		tbl=new Object[rows][8];
		for(int i=0;rs.next();i++)
			for(int j=1;j<9;j++)
			{
				if(j==7)
					tbl[i][j-1]=rs.getBoolean(j);
				else
					tbl[i][j-1]=rs.getObject(j);
			}
		}   
		catch (SQLException e) {e.printStackTrace();}
		return tbl;
	}
	/**
	 * gets income of desirable quarter
	 * @param quarter
	 * @param conn
	 * @return  report id,item name,color,quantity,income,created date
	 */
	public Object[][] mngQuartIncRep(String quarter,Connection conn)
	{
		Object[][] tbl=null;
		int first=0;
		int second=0;
		Calendar cal = Calendar.getInstance();
		int year = cal.get(Calendar.YEAR);
		if(quarter.equals("1"))
		{
			first=1;
			second=3;	
		}
		if(quarter.equals("2"))
		{
			first=4;
			second=6;	
		}
		if(quarter.equals("3"))
		{
			first=7;
			second=9;	
		}if(quarter.equals("4"))
		{
			first=10;
			second=12;	
		}
		
		try {
		stmt=conn.createStatement();
		ResultSet rs=stmt.executeQuery("SELECT * FROM quarterincomereport WHERE (MONTH(date)>='"+first+"') AND (MONTH(date)<='"+second+"') AND (year(date)='"+year+"')");
		rs.last();
		int rows=rs.getRow();
		rs.beforeFirst();
		tbl=new Object[rows][6];
		for(int i=0;rs.next();i++)
			for(int j=1;j<7;j++)
			{
				tbl[i][j-1]=rs.getObject(j);
			}
		}   
		catch (SQLException e) {e.printStackTrace();}
		return tbl;
	}

	/**
	 * Gets data of the complaints report for the manager
	 * @param conn
	 * @return data of complaints report
	 */
	public Object[][] mngCompRep(Connection conn)
	{
		Object[][] tbl=null;
		try {
		stmt=conn.createStatement();
		ResultSet rs=stmt.executeQuery("SELECT * FROM complaintreport");
		rs.last();
		int rows=rs.getRow();
		tbl=new Object[rows][9];
		for(int i=0;rs.next();i++)
		{
			for(int j=1;j<10;j++)
			{
				if(j==8)
					tbl[i][j-1]=!rs.getBoolean(j);
				else
					tbl[i][j-1]=rs.getObject(j);
			}
		}
		}   
		catch (SQLException e) {e.printStackTrace();}
		return tbl;
	}
	/**
	 * returns the selected credit card details , null if fails
	 * @param msg= CreditCard (only uid and card number)
	 * @param conn
	 * @return CreditCard (expire date)
	 */
	public Object getSelectedCreditCard(Object msg,Connection conn) 
	{
		CreditCard cc=(CreditCard)msg;
		try
		{
			stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT * FROM creditcard c WHERE c.uid='"+(Integer)cc.uid+"'AND cardnumber='"+cc.getcardNumber()+"'");
			rs.next();
			String str=rs.getString(3);
			String [] split=new String [2];
			split=str.split("/");
			cc.setexpireMonth(split[0]);
			cc.setexpireYear(split[1]);
			return cc;
		}
		catch (SQLException e) {System.out.println("delivery remove : SQL ERROR! ");}
		return null;
	}
	/**
	 * gets the user credit card number , and delete the expired numbers
	 * @param uid
	 * @param conn
	 * @return
	 */
	public Object getUpdatedCreditCardNumbers(int uid,Connection conn) //returns array of the user's not expired credit cards
	{
		
		CreditCard cc=new CreditCard(uid);
		int i=0;
		String[] split=new String[2];
		try
		{
			stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT * FROM creditcard c WHERE c.uid='"+uid+"'");
			rs.last();
			int rows=rs.getRow();
			String creditnumber[]=new String[rows];
			String expirednumber[]=new String[rows];
			rs.beforeFirst();
			while(rs.next())
			{	
				String str=rs.getString(3);//expire date
				split=str.split("/");
				cc.setexpireMonth(split[0]);
				cc.setexpireYear(split[1]);
				int flag=cc.validateExpireDate();
				if(flag==1)	creditnumber[i]=rs.getString(2);//not expired
				else
				{// delete expired
					expirednumber[i]=rs.getString(2);
					stmt.executeUpdate("DELETE FROM creditcard  WHERE (uid='"+uid+"')and('"+expirednumber[i]+"'=cardnumber)");
				}
				i++;
			}
			return creditnumber;
		}
		catch (SQLException e) {System.out.println(" getUpdatedCreditCardNumber : SQL ERROR! ");}
		return null;
	}

	/**
	 * gets all order that were made in the desirable month
	 * @param month
	 * @param conn
	 * @return orders details : order id,item id,name,color,quantity and if paid
	 */
	public Object getOrderReport(String month, Connection conn)
	{
		Object[][] tbl=null;
		int mon = Integer.parseInt(month);
		ResultSet rs=null;
		try {
		stmt=conn.createStatement();
		if(mon==1)
			 rs=stmt.executeQuery("SELECT oid,itemid,name,color,quantity,paid FROM((SELECT DISTINCT o.oid,i.name,i.color,u.itemid,u.quantity,o.createddate,u.paid FROM `order` o,usercart u,item i WHERE (o.oid=u.oid) AND (u.itemid=i.id) ORDER BY o.oid) UNION (SELECT DISTINCT o.oid,i.name,i.color,ic.itemid,ic.itemquantity as quantity,o.createddate,ic.paid FROM `order` o,itemforcustomitem ic,item i WHERE (o.oid=ic.oid) AND (ic.itemid=i.id) ORDER BY o.oid))hhhh WHERE createddate LIKE '%/01/%' ORDER BY oid");
		if(mon==2)
			 rs=stmt.executeQuery("SELECT oid,itemid,name,color,quantity,paid FROM((SELECT DISTINCT o.oid,i.name,i.color,u.itemid,u.quantity,o.createddate,u.paid FROM `order` o,usercart u,item i WHERE (o.oid=u.oid) AND (u.itemid=i.id) ORDER BY o.oid) UNION (SELECT DISTINCT o.oid,i.name,i.color,ic.itemid,ic.itemquantity as quantity,o.createddate,ic.paid FROM `order` o,itemforcustomitem ic,item i WHERE (o.oid=ic.oid) AND (ic.itemid=i.id) ORDER BY o.oid))hhhh WHERE createddate LIKE '%/02/%' ORDER BY oid");
		if(mon==3)
			 rs=stmt.executeQuery("SELECT oid,itemid,name,color,quantity,paid FROM((SELECT DISTINCT o.oid,i.name,i.color,u.itemid,u.quantity,o.createddate,u.paid FROM `order` o,usercart u,item i WHERE (o.oid=u.oid) AND (u.itemid=i.id) ORDER BY o.oid) UNION (SELECT DISTINCT o.oid,i.name,i.color,ic.itemid,ic.itemquantity as quantity,o.createddate,ic.paid FROM `order` o,itemforcustomitem ic,item i WHERE (o.oid=ic.oid) AND (ic.itemid=i.id) ORDER BY o.oid))hhhh WHERE createddate LIKE '%/03/%' ORDER BY oid");
		if(mon==4)
			 rs=stmt.executeQuery("SELECT oid,itemid,name,color,quantity,paid FROM((SELECT DISTINCT o.oid,i.name,i.color,u.itemid,u.quantity,o.createddate,u.paid FROM `order` o,usercart u,item i WHERE (o.oid=u.oid) AND (u.itemid=i.id) ORDER BY o.oid) UNION (SELECT DISTINCT o.oid,i.name,i.color,ic.itemid,ic.itemquantity as quantity,o.createddate,ic.paid FROM `order` o,itemforcustomitem ic,item i WHERE (o.oid=ic.oid) AND (ic.itemid=i.id) ORDER BY o.oid))hhhh WHERE createddate LIKE '%/04/%' ORDER BY oid");
		if(mon==5)
			 rs=stmt.executeQuery("SELECT oid,itemid,name,color,quantity,paid FROM((SELECT DISTINCT o.oid,i.name,i.color,u.itemid,u.quantity,o.createddate,u.paid FROM `order` o,usercart u,item i WHERE (o.oid=u.oid) AND (u.itemid=i.id) ORDER BY o.oid) UNION (SELECT DISTINCT o.oid,i.name,i.color,ic.itemid,ic.itemquantity as quantity,o.createddate,ic.paid FROM `order` o,itemforcustomitem ic,item i WHERE (o.oid=ic.oid) AND (ic.itemid=i.id) ORDER BY o.oid))hhhh WHERE createddate LIKE '%/05/%' ORDER BY oid");
		if(mon==6)
			 rs=stmt.executeQuery("SELECT oid,itemid,name,color,quantity,paid FROM((SELECT DISTINCT o.oid,i.name,i.color,u.itemid,u.quantity,o.createddate,u.paid FROM `order` o,usercart u,item i WHERE (o.oid=u.oid) AND (u.itemid=i.id) ORDER BY o.oid) UNION (SELECT DISTINCT o.oid,i.name,i.color,ic.itemid,ic.itemquantity as quantity,o.createddate,ic.paid FROM `order` o,itemforcustomitem ic,item i WHERE (o.oid=ic.oid) AND (ic.itemid=i.id) ORDER BY o.oid))hhhh WHERE createddate LIKE '%/06/%' ORDER BY oid");
		if(mon==7)
			 rs=stmt.executeQuery("SELECT oid,itemid,name,color,quantity,paid FROM((SELECT DISTINCT o.oid,i.name,i.color,u.itemid,u.quantity,o.createddate,u.paid FROM `order` o,usercart u,item i WHERE (o.oid=u.oid) AND (u.itemid=i.id) ORDER BY o.oid) UNION (SELECT DISTINCT o.oid,i.name,i.color,ic.itemid,ic.itemquantity as quantity,o.createddate,ic.paid FROM `order` o,itemforcustomitem ic,item i WHERE (o.oid=ic.oid) AND (ic.itemid=i.id) ORDER BY o.oid))hhhh WHERE createddate LIKE '%/07/%' ORDER BY oid");
		if(mon==8)
			 rs=stmt.executeQuery("SELECT oid,itemid,name,color,quantity,paid FROM((SELECT DISTINCT o.oid,i.name,i.color,u.itemid,u.quantity,o.createddate,u.paid FROM `order` o,usercart u,item i WHERE (o.oid=u.oid) AND (u.itemid=i.id) ORDER BY o.oid) UNION (SELECT DISTINCT o.oid,i.name,i.color,ic.itemid,ic.itemquantity as quantity,o.createddate,ic.paid FROM `order` o,itemforcustomitem ic,item i WHERE (o.oid=ic.oid) AND (ic.itemid=i.id) ORDER BY o.oid))hhhh WHERE createddate LIKE '%/08/%' ORDER BY oid");
		if(mon==9)
			 rs=stmt.executeQuery("SELECT oid,itemid,name,color,quantity,paid FROM((SELECT DISTINCT o.oid,i.name,i.color,u.itemid,u.quantity,o.createddate,u.paid FROM `order` o,usercart u,item i WHERE (o.oid=u.oid) AND (u.itemid=i.id) ORDER BY o.oid) UNION (SELECT DISTINCT o.oid,i.name,i.color,ic.itemid,ic.itemquantity as quantity,o.createddate,ic.paid FROM `order` o,itemforcustomitem ic,item i WHERE (o.oid=ic.oid) AND (ic.itemid=i.id) ORDER BY o.oid))hhhh WHERE createddate LIKE '%/09/%' ORDER BY oid");
		if(mon==10)
			 rs=stmt.executeQuery("SELECT oid,itemid,name,color,quantity,paid FROM((SELECT DISTINCT o.oid,i.name,i.color,u.itemid,u.quantity,o.createddate,u.paid FROM `order` o,usercart u,item i WHERE (o.oid=u.oid) AND (u.itemid=i.id) ORDER BY o.oid) UNION (SELECT DISTINCT o.oid,i.name,i.color,ic.itemid,ic.itemquantity as quantity,o.createddate,ic.paid FROM `order` o,itemforcustomitem ic,item i WHERE (o.oid=ic.oid) AND (ic.itemid=i.id) ORDER BY o.oid))hhhh WHERE createddate LIKE '%/10/%' ORDER BY oid");
		if(mon==11)
			 rs=stmt.executeQuery("SELECT oid,itemid,name,color,quantity,paid FROM((SELECT DISTINCT o.oid,i.name,i.color,u.itemid,u.quantity,o.createddate,u.paid FROM `order` o,usercart u,item i WHERE (o.oid=u.oid) AND (u.itemid=i.id) ORDER BY o.oid) UNION (SELECT DISTINCT o.oid,i.name,i.color,ic.itemid,ic.itemquantity as quantity,o.createddate,ic.paid FROM `order` o,itemforcustomitem ic,item i WHERE (o.oid=ic.oid) AND (ic.itemid=i.id) ORDER BY o.oid))hhhh WHERE createddate LIKE '%/11/%' ORDER BY oid");
		if(mon==12)
			 rs=stmt.executeQuery("SELECT oid,itemid,name,color,quantity,paid FROM((SELECT DISTINCT o.oid,i.name,i.color,u.itemid,u.quantity,o.createddate,u.paid FROM `order` o,usercart u,item i WHERE (o.oid=u.oid) AND (u.itemid=i.id) ORDER BY o.oid) UNION (SELECT DISTINCT o.oid,i.name,i.color,ic.itemid,ic.itemquantity as quantity,o.createddate,ic.paid FROM `order` o,itemforcustomitem ic,item i WHERE (o.oid=ic.oid) AND (ic.itemid=i.id) ORDER BY o.oid))hhhh WHERE createddate LIKE '%/12/%' ORDER BY oid");
	
		rs.last();
		int rows=rs.getRow();
		rs.beforeFirst();
		tbl=new Object[rows][6];
		for(int i=0;rs.next();i++)
			for(int j=1;j<7;j++)
			{
				if(j==6)
					tbl[i][j-1]=rs.getBoolean(j);
				else
					tbl[i][j-1]=rs.getObject(j);
			}
		}   
		catch (SQLException e) {e.printStackTrace();}
		return tbl;
	}

	/**
	 * Display balance
	 * @param conn
	 * @param id member ID
	 * @return balance value
	 */
	public int checkSub(Connection conn,String id)
	{
		try {
			stmt=conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT debt FROM member WHERE uid="+id);
			rs.next();
			return rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
			return -999;
		}
	}
/**
 * sets the delivery detail in data base
 * @param sn
 * @param conn
 */
	public void setDeliveryDetails(String[] sn, Connection conn) 
	{
		String query = "INSERT INTO delivery ("
		    + " oid,"
		    + " recipientaddress,"
		    + " recipientname,"
		    + " recipientphonnumber) VALUES ("
		    + "?, ?, ?, ?)";
		
		try {
			
			 PreparedStatement st = conn.prepareStatement(query);
			 st.setInt(1,Integer.parseInt(sn[4]));
			 st.setString(2, sn[2]);
			 st.setString(3, sn[1]);
			 st.setString(4, (sn[3]));
			 st.executeUpdate();
			 st.close();
			 
		
		} catch (SQLException e) {e.printStackTrace();}
	}


/**
 * 	sets into flag the compenseion  amount 
 * @param orderid- order id
 * @param conn - sql connection
 * @return object array: {uid, order totalprice,flag}
 */
	public Object cancelCheck(int orderid, Connection conn)
	{
		Object[] order=new Object[3];
		SimpleDateFormat d=new SimpleDateFormat("dd");
		SimpleDateFormat m=new SimpleDateFormat("MM");
		SimpleDateFormat y=new SimpleDateFormat("yyyy");
		Calendar c=Calendar.getInstance();
		
		String date[]=new String[3];
		String time[]=new String[3];
		String r_date=null;
		String r_time=null;
		
		int diffh;
		int diffm;
		Integer dateInt[]=new Integer[3];
		Integer timeInt[]=new Integer[3];
		int year=Integer.parseInt(y.format(c.getTime()));
		int day=Integer.parseInt(d.format(c.getTime()));
		int month=Integer.parseInt(m.format(c.getTime()));
		int hour=d.getCalendar().get(c.HOUR);
		int minutes=d.getCalendar().get(c.MINUTE);
		try 
		{
			stmt = conn.createStatement();
			ResultSet rs=stmt.executeQuery("SELECT uid,totalprice,readydate,readytime FROM test.order  WHERE oid='"+orderid+"'"); 
			if(rs.next())
			{
				order[0]=rs.getInt(1);
				order[1]=rs.getObject(2);
				r_date=rs.getString(3);
				r_time=rs.getString(4);
				date=r_date.split("/");
				time=r_time.split(":");
			for(int i=0;i<3;i++)
			{
				dateInt[i]=Integer.parseInt(date[i]);
				timeInt[i]=Integer.parseInt(time[i]);
			}
			
			if(dateInt[2]<year){order[2]= 0;return order;}
			if(dateInt[2]>year){order[2]= 1;return order;}
			else//(year==dateInt[2])
			{
				if(dateInt[1]<month){order[2]= 0;return order;}
				if(dateInt[1]>month){order[2]= 1;return order;}
				else//month==dateInt[1]
				{
					if(dateInt[0]<day){order[2]= 0;return order;}
					if(dateInt[0]>day){order[2]= 1;return order;}
					else//day==dateInt[0]
					{
						if(timeInt[0]<hour){order[2]= 0;return order;}
						if(timeInt[0]>hour) 
						{
							diffh=timeInt[0]-hour;
							if(diffh>1&diffh<=3){order[2]= 2;return order;}
							else 
							{
								if(diffh==1)
								{
									diffm=timeInt[1]- minutes;
									if(diffm>0){order[2]= 2;return order;}
									else {order[2]= 3;return order;}
								}
								else	{order[2]= 3;return order;}
							}
						}
						else//hour==timeInt[1]
							diffm=timeInt[1]-minutes;
						if(diffm<=0){order[2]= 0;return order;}
						else {order[2]= 3;return order;}
					}
				}
			}
		}
		}catch (SQLException e) {e.printStackTrace();}
		order[2]= 0;
		return (Object)order;		
	}
/**
 * remove order from database
 * @param int1 - the order id
 * @param conn - sql connection
 * @return
 */
	public Object removeOrder(int int1, Connection conn) 
	{
		try{
			stmt = conn.createStatement();
			stmt.executeUpdate("DELETE FROM usercart WHERE oid='"+int1+"'");
			stmt.executeUpdate("DELETE FROM itemforcustomitem WHERE oid='"+int1+"'");
			stmt.executeUpdate("DELETE FROM delivery  WHERE oid='"+int1+"'");
		}catch (SQLException e) {e.printStackTrace();}
		return null;
	}

	
}